Re: Index to help ordering?

--As of January 20, 2014 10:35:41 AM +0000, James David Smith is alleged to
have said:

> Basically I have about 75,000 people (ppid) and a record for each
> minute of the day for each person. So 24 hours x 60 minutes x 75,000
> people.
>
> I 'normally' want to just view one person's 'day' at a time. So I
> might do something like this:
>
> SELECT ppid, point_time, mode, concentration FROM table WHERE ppid =
> '43' ORDER BY point_time;
>
> Does this revise how you think I should go about speeding up this query?

--As for the rest, it is mine.

Quite a bit.  ;)  First off: If you only want to view one day at a time,
I'd put that into the WHERE clause.  This could be done several ways:
BETWEEN start and end time of the day, or you could reformat to exclude the
hours/minutes, etc.  (If it's only the most recent day, easiest is probably
to ask for anything newer than midnight last night - or whenever your day
begins.)

Secondly, I'd probably advise two indexes: One on ppid, and one on however
you decide to select the time.  (Note that you can create an index on a
function: if you wanted to use 'date_trunc' to get the day, you could
create an index on `date_trunc('day', point_time)`, and a WHERE clause
would use that index instead of computing it for each item at query time.)

Once I'd done that (and run EXPLAIN, etc. to see how things are going.), if
things were still too slow I might think about other options.  You might
think about partitioning the table on either the ppid or the point_time,
depending on how easy it is to set up and how good you are at automating
the partitioning process.  (You have to create a sub-table for each
partition, and how you divide things best would take some fiddling.)

But I think if you put the time into the query and create those two
indexes, you'll find things are likely fast enough.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------